﻿using Agile.Infra.Core.Exceptions;
using NPOI.SS.UserModel;
using System.Data;

namespace Agile.Infra.Core.Agile
{
    public class NPOIHelper
    {
        /// <summary>
        /// 将excel文件内容读取到DataTable数据表中
        /// </summary>
        /// <param name="fileName">文件完整路径名</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="titleIndexRow">列名行数</param>
        /// <returns>DataTable数据表</returns>
        public static DataTable ReadExcelToDataTable(string fileName, Func<byte[], string, string> uploadImageFunc = null, string sheetName = null, int titleIndexRow = 1)
        {
            try
            {
                if (!File.Exists(fileName)) return null;

                //根据指定路径读取文件
                FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);

                return ReadStreamToDataTable(fs, uploadImageFunc, sheetName, titleIndexRow);
            }
            catch (BusinessException) { throw; }
            catch (Exception)
            {
                throw;
            }
        }

        /// <summary>
        /// 将文件流读取到DataTable数据表中
        /// </summary>
        /// <param name="fileStream">文件流</param>
        /// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
        /// <param name="titleIndexRow">列名行数</param>
        /// <returns>DataTable数据表</returns>
        public static DataTable ReadStreamToDataTable(Stream fileStream, Func<byte[], string, string> uploadImageFunc = null, string sheetName = null, int titleIndexRow = 1)
        {
            try
            {
                //定义要返回的datatable对象
                DataTable data = new DataTable();

                //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
                IWorkbook workbook = WorkbookFactory.Create(fileStream);

                //excel工作表
                ISheet sheet = null;

                //如果有指定工作表名称
                if (!string.IsNullOrEmpty(sheetName))
                {
                    sheet = workbook.GetSheet(sheetName);
                }
                if (sheet == null) sheet = workbook.GetSheetAt(0);

                //数据开始行
                int startRow = sheet.FirstRowNum;

                if (sheet != null)
                {
                    IRow firstRow = sheet.GetRow(titleIndexRow - 1);
                    //一行最后一个cell的编号 即总的列数
                    int cellCount = firstRow.LastCellNum;
                    //如果有标题列名
                    if (titleIndexRow > 0)
                    {
                        for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                        {
                            ICell cell = firstRow.GetCell(i);
                            if (cell != null)
                            {
                                string cellValue = cell.ToString();
                                if (cellValue != null)
                                {
                                    DataColumn column = new DataColumn(cellValue);
                                    data.Columns.Add(column);
                                }
                            }
                        }
                        // 排除标题行
                        startRow += titleIndexRow;
                    }

                    //最后一列的标号
                    int rowCount = sheet.LastRowNum;

                    //获取图片
                    var isExistsImage = false;
                    var pictureList = new List<PicturesInfo>();
                    if (uploadImageFunc != null)
                    {
                        pictureList = sheet.GetAllPictureInfos();

                        isExistsImage = (pictureList != null && pictureList.Any());
                    }

                    for (int i = startRow; i <= rowCount; ++i)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row == null || row.FirstCellNum < 0 || row.Cells.All(c => c.CellType == CellType.Blank)) continue; //没有数据的行默认是null　　　　　　　

                        DataRow dataRow = data.NewRow();
                        for (int j = row.FirstCellNum; j < cellCount; ++j)
                        {
                            //同理，没有数据的单元格都默认是null
                            ICell cell = row.GetCell(j);
                            if (cell != null)
                            {
                                if (cell.CellType == CellType.Numeric)
                                {
                                    //判断是否日期类型
                                    if (DateUtil.IsCellDateFormatted(cell))
                                    {
                                        dataRow[j] = row.GetCell(j).DateCellValue;
                                    }
                                    else
                                    {
                                        dataRow[j] = row.GetCell(j).ToString().Trim();
                                    }
                                }
                                else
                                {
                                    dataRow[j] = row.GetCell(j).ToString().Trim();

                                    if (isExistsImage && string.IsNullOrWhiteSpace(dataRow[j].ToString()))
                                    {
                                        var picture = pictureList.FirstOrDefault(x => x.MinCol == j && x.MinRow == i);

                                        if (picture != null)
                                        {
                                            dataRow[j] = uploadImageFunc(picture.PictureData, picture.PictureType.GetExtendName());
                                        }
                                    }
                                }
                            }
                        }
                        data.Rows.Add(dataRow);
                    }
                }
                return data;
            }
            catch (BusinessException) { throw; }
            catch (Exception ex)
            {
                throw new BusinessException("读取Excel异常：" + ex.Message);
            }
        }

        /// <summary>获取execl单元格</summary>
        public static async Task<Dictionary<int, List<ICell>>> GetExcelCells(Stream fileStream, Func<byte[], string, Task<string>> uploadImageFunc = null)
        {
            try
            {
                //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
                var workbook = WorkbookFactory.Create(fileStream);
                return await GetExcelCells(workbook, uploadImageFunc);
            }
            catch (BusinessException) { throw; }
            catch (Exception ex)
            {
                throw new BusinessException("读取Excel异常：" + ex.Message);
            }
        }

        /// <summary>获取execl单元格</summary>
        public static async Task<Dictionary<int, List<ICell>>> GetExcelCells(byte[] byteArray, Func<byte[], string, Task<string>> uploadImageFunc = null)
        {
            try
            {
                using (var ms = new MemoryStream(byteArray))
                {
                    var workbook = WorkbookFactory.Create(ms);
                    return await GetExcelCells(workbook, uploadImageFunc);
                }
            }
            catch (BusinessException) { throw; }
            catch (Exception ex)
            {
                throw new BusinessException("读取Excel异常：" + ex.Message);
            }
        }

        private static async Task<Dictionary<int, List<ICell>>> GetExcelCells(IWorkbook workbook, Func<byte[], string, Task<string>> uploadImageFunc = null)
        {
            Dictionary<int, List<ICell>> keyValues = new Dictionary<int, List<ICell>>();
            try
            {
                //根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
                //excel工作表
                ISheet sheet = null;
                int sheetNumber = workbook.NumberOfSheets;

                for (int i = 0; i < sheetNumber; i++)
                {
                    List<ICell> listCell = new List<ICell>();
                    if (i > 0 || sheet == null)
                        sheet = workbook.GetSheetAt(i);

                    if (sheet == null || sheet.GetRow(0) == null) continue;

                    //获取图片
                    var isExistsImage = false;
                    var pictureList = new List<PicturesInfo>();
                    if (uploadImageFunc != null)
                    {
                        pictureList = sheet.GetAllPictureInfos();

                        isExistsImage = (pictureList != null && pictureList.Any());
                    }

                    //最后一行的标号
                    int rowCount = sheet.LastRowNum;
                    for (int r = 0; r <= rowCount; ++r)
                    {
                        IRow row = sheet.GetRow(r);

                        if (row == null || row.FirstCellNum < 0) continue; //没有数据的行默认是null　　　　　　　

                        //如果有图片先上传图片 
                        if (uploadImageFunc != null && isExistsImage)
                        {
                            for (int c = row.FirstCellNum; c < row.LastCellNum; ++c)
                            {
                                ICell cell = row.GetCell(c);
                                if (cell == null) continue;

                                if (i == 0 && r == 0 && c == 0) continue; //忽略第一个单元格的图片

                                if (pictureList.Exists(x => x.MinCol == c && x.MinRow == r))
                                {
                                    var picture = pictureList.FirstOrDefault(x => x.MinCol == c && x.MinRow == r);

                                    if (picture != null)
                                    {
                                        cell.SetCellValue(await uploadImageFunc(picture.PictureData, picture.PictureType.GetExtendName()));
                                    }
                                }
                            }
                        }

                        //去空格
                        row.Cells.ForEach(m => m.SetCellValue(m.ToString().Trim()));

                        listCell.AddRange(row.Cells);
                    }
                    keyValues.Add(i, listCell);
                }

                return keyValues;
            }
            catch (BusinessException) { throw; }
            catch (Exception ex)
            {
                throw new BusinessException("读取Excel异常：" + ex.Message);
            }
        } 
    }
}
